-- @owner: hejiahuan11
-- @date: 2025-9-20
-- @testpoint: 单独调用、存储过程和自定义函数分别执行，合理报错
--step1: 连接带shark插件的D库; expect: 成功
@conn PrimaryDbDmode;

--step2: 创建存储过程和自定义函数; expect: 成功
-- 2.1 针对ISNULL函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_isnull(INT, INT, OUT INT);
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_isnull(IN p1 INT, IN p2 INT, OUT res INT)
LANGUAGE plpgsql AS $$
BEGIN
    res := ISNULL(p1, p2);
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_isnull(INT, INT, OUT INT);
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_isnull(p1 INT, p2 INT) RETURNS INT
LANGUAGE plpgsql AS $$
BEGIN
    RETURN ISNULL(p1, p2);
END $$;
/

-- 2.2 针对ATN2函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_atn2(NUMERIC, NUMERIC, OUT NUMERIC);
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_atn2(IN p1 NUMERIC, IN p2 NUMERIC, OUT res NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    res := ATN2(p1, p2);
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_atn2(NUMERIC, NUMERIC, OUT NUMERIC);
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_atn2(p1 NUMERIC, p2 NUMERIC) RETURNS NUMERIC
LANGUAGE plpgsql AS $$
BEGIN
    RETURN ATN2(p1, p2);
END $$;
/

-- 2.3 针对LOG10函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_log10(NUMERIC, OUT NUMERIC);
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_log10(IN p1 NUMERIC, OUT res NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    res := LOG10(p1);
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_log10(NUMERIC, OUT NUMERIC);
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_log10(p1 NUMERIC) RETURNS NUMERIC
LANGUAGE plpgsql AS $$
BEGIN
    RETURN LOG10(p1);
END $$;
/

-- 2.4 针对DATEDIFF(year)函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff(DATE, DATE, OUT INT);
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff(IN d1 DATE, IN d2 DATE, OUT res INT)
LANGUAGE plpgsql AS $$
BEGIN
    res := DATEDIFF(year, d1, d2);
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff(DATE, DATE, OUT INT);
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff(d1 DATE, d2 DATE) RETURNS INT
LANGUAGE plpgsql AS $$
BEGIN
    RETURN DATEDIFF(year, d1, d2);
END $$;
/

-- 2.5 针对DATEDIFF_BIG(year)函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff_big(DATE, DATE, OUT BIGINT);
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff_big(IN d1 DATE, IN d2 DATE, OUT res BIGINT)
LANGUAGE plpgsql AS $$
BEGIN
    res := DATEDIFF_BIG(year, d1, d2);
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff_big(DATE, DATE, OUT BIGINT);
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff_big(d1 DATE, d2 DATE) RETURNS BIGINT
LANGUAGE plpgsql AS $$
BEGIN
    RETURN DATEDIFF_BIG(year, d1, d2);
END $$;
/

-- 2.6 针对CHARINDEX函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_charindex(TEXT, TEXT, OUT INT);
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_charindex(IN str1 TEXT, IN str2 TEXT, OUT res INT)
LANGUAGE plpgsql AS $$
BEGIN
    res := CHARINDEX(str1, str2);
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_charindex(TEXT, TEXT, OUT INT);
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_charindex(str1 TEXT, str2 TEXT) RETURNS INT
LANGUAGE plpgsql AS $$
BEGIN
    RETURN CHARINDEX(str1, str2);
END $$;
/

-- 2.7 针对CAST(9/2 AS CHAR)函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_cast(OUT CHAR);
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_cast(OUT res CHAR)
LANGUAGE plpgsql AS $$
BEGIN
    res := CAST(9/2 AS CHAR);
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_cast(OUT CHAR);
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_cast() RETURNS CHAR
LANGUAGE plpgsql AS $$
BEGIN
    RETURN CAST(9/2 AS CHAR);
END $$;
/

-- 2.8 针对TRY_CAST函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_try_cast(TEXT, OUT NUMERIC(10,5));
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_try_cast(IN str TEXT, OUT res NUMERIC(10,5))
LANGUAGE plpgsql AS $$
BEGIN
    res := TRY_CAST(str AS NUMERIC(10,5));
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_try_cast(TEXT, OUT NUMERIC(10,5));
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_try_cast(str TEXT) RETURNS NUMERIC(10,5)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN TRY_CAST(str AS NUMERIC(10,5));
END $$;
/

-- 2.9 针对CONVERT函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_convert(INT, OUT VARCHAR);
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_convert(IN num INT, OUT res VARCHAR)
LANGUAGE plpgsql AS $$
BEGIN
    res := CONVERT(VARCHAR, num);
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_convert(INT, OUT VARCHAR);
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_convert(num INT) RETURNS VARCHAR
LANGUAGE plpgsql AS $$
BEGIN
    RETURN CONVERT(VARCHAR, num);
END $$;
/

-- 2.10 针对TRY_CONVERT函数
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_try_convert(INT, OUT VARCHAR);
CREATE PROCEDURE proc_D_Function_FOURTH_COMPATIBILITY_Case0012_try_convert(IN num INT, OUT res VARCHAR)
LANGUAGE plpgsql AS $$
BEGIN
    res := TRY_CONVERT(VARCHAR, num);
END $$;
/

DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_try_convert(INT, OUT VARCHAR);
CREATE FUNCTION func_D_Function_FOURTH_COMPATIBILITY_Case0012_try_convert(num INT) RETURNS VARCHAR
LANGUAGE plpgsql AS $$
BEGIN
    RETURN TRY_CONVERT(VARCHAR, num);
END $$;
/

--step3: 创建结果记录表; expect: 成功
CREATE TABLE D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (
    id SERIAL PRIMARY KEY,
    function_name TEXT NOT NULL,
    call_type TEXT NOT NULL,
    actual_result TEXT,
    expected_result TEXT NOT NULL
);

--step4: 执行三种调用方式并记录结果; expect: 成功--
-- 4.1 ISNULL函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('ISNULL', '单独调用', CAST(ISNULL(1, 2) AS TEXT), '1'),
    ('ISNULL', '自定义函数', CAST(func_D_Function_FOURTH_COMPATIBILITY_Case0012_isnull(1, 2) AS TEXT), '1');

DECLARE res INT;BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_isnull(1, 2, res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'ISNULL', '存储过程', CAST(res AS TEXT), '1');
END;
/

-- 4.2 ATN2函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('ATN2', '单独调用', CAST(ROUND(ATN2(1, 2), 6) AS TEXT), '.463648'),
    ('ATN2', '自定义函数', CAST(ROUND(func_D_Function_FOURTH_COMPATIBILITY_Case0012_atn2(1, 2), 6) AS TEXT), '.463648');

DECLARE res NUMERIC; BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_atn2(1, 2, res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'ATN2', '存储过程', CAST(ROUND(res, 6) AS TEXT), '.463648');
END;
/

-- 4.3 LOG10函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('LOG10', '单独调用', CAST(LOG10(1) AS TEXT), '0'),
    ('LOG10', '自定义函数', CAST(func_D_Function_FOURTH_COMPATIBILITY_Case0012_log10(1) AS TEXT), '0');

DECLARE res NUMERIC; BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_log10(1, res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'LOG10', '存储过程', CAST(res AS TEXT), '0');
END;
/

-- 4.4 DATEDIFF函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('DATEDIFF', '单独调用', CAST(DATEDIFF(year, '2025-01-01', '2026-01-02') AS TEXT), '1'),
    ('DATEDIFF', '自定义函数', CAST(func_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff('2025-01-01', '2026-01-02') AS TEXT), '1');

DECLARE res INT; BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff('2025-01-01', '2026-01-02', res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'DATEDIFF', '存储过程', CAST(res AS TEXT), '1');
END;
/

-- 4.5 DATEDIFF_BIG函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('DATEDIFF_BIG', '单独调用', CAST(DATEDIFF_BIG(year, '2025-01-01', '2026-01-02') AS TEXT), '1'),
    ('DATEDIFF_BIG', '自定义函数', CAST(func_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff_big('2025-01-01', '2026-01-02') AS TEXT), '1');

DECLARE res BIGINT; BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff_big('2025-01-01', '2026-01-02', res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'DATEDIFF_BIG', '存储过程', CAST(res AS TEXT), '1');
END;
/

-- 4.6 CHARINDEX函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('CHARINDEX', '单独调用', CAST(CHARINDEX('1', 'abc123xyz') AS TEXT), '4'),
    ('CHARINDEX', '自定义函数', CAST(func_D_Function_FOURTH_COMPATIBILITY_Case0012_charindex('1', 'abc123xyz') AS TEXT), '4');

DECLARE res INT; BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_charindex('1', 'abc123xyz', res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'CHARINDEX', '存储过程', CAST(res AS TEXT), '4');
END;
/

-- 4.7 CAST(9/2 AS CHAR)函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('CAST(9/2 AS CHAR)', '单独调用', CAST(CAST(9/2 AS CHAR) AS TEXT), '4.5'),
    ('CAST(9/2 AS CHAR)', '自定义函数', CAST(func_D_Function_FOURTH_COMPATIBILITY_Case0012_cast() AS TEXT), '4.5');

DECLARE res CHAR; BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_cast(res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'CAST(9/2 AS CHAR)', '存储过程', CAST(res AS TEXT), '4.5');
END;
/

-- 4.8 TRY_CAST函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('TRY_CAST', '单独调用', CAST(TRY_CAST('1.123456789' AS NUMERIC(10,5)) AS TEXT), '1.12346'),
    ('TRY_CAST', '自定义函数', CAST(func_D_Function_FOURTH_COMPATIBILITY_Case0012_try_cast('1.123456789') AS TEXT), '1.12346');

DECLARE res NUMERIC(10,5); BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_try_cast('1.123456789', res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'TRY_CAST', '存储过程', CAST(res AS TEXT), '1.12346');
END;
/

-- 4.9 CONVERT函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('CONVERT', '单独调用', CAST(CONVERT(VARCHAR, 123456) AS TEXT), '123456'),
    ('CONVERT', '自定义函数', CAST(func_D_Function_FOURTH_COMPATIBILITY_Case0012_convert(123456) AS TEXT), '123456');

DECLARE res VARCHAR; BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_convert(123456, res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'CONVERT', '存储过程', CAST(res AS TEXT), '123456');
END;
/

-- 4.10 TRY_CONVERT函数测试
INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results (function_name, call_type, actual_result, expected_result)
VALUES
    ('TRY_CONVERT', '单独调用', CAST(TRY_CONVERT(VARCHAR, 123456) AS TEXT), '123456'),
    ('TRY_CONVERT', '自定义函数', CAST(func_D_Function_FOURTH_COMPATIBILITY_Case0012_try_convert(123456) AS TEXT), '123456');

DECLARE res VARCHAR; BEGIN
    PERFORM proc_D_Function_FOURTH_COMPATIBILITY_Case0012_try_convert(123456, res);
    INSERT INTO D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
    VALUES (nextval('D_Function_FOURTH_COMPATIBILITY_Case0012_test_results_id_seq'), 'TRY_CONVERT', '存储过程', CAST(res AS TEXT), '123456');
END;
/

--step5: 验证结果; expect: 成功--
SELECT
    function_name,
    call_type,
    actual_result,
    expected_result,
    '结果不一致' AS status
FROM D_Function_FOURTH_COMPATIBILITY_Case0012_test_results
WHERE actual_result <> expected_result;

--step7: 清理环境; expect: 成功
-- 7.1 删除ISNULL相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_isnull(INT, INT, OUT INT);
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_isnull(INT, INT);

-- 7.2 删除ATN2相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_atn2(NUMERIC, NUMERIC, OUT NUMERIC);
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_atn2(NUMERIC, NUMERIC);

-- 7.3 删除LOG10相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_log10(NUMERIC, OUT NUMERIC);
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_log10(NUMERIC);

-- 7.4 删除DATEDIFF(year)相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff(DATE, DATE, OUT INT);
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff(DATE, DATE);

-- 7.5 删除DATEDIFF_BIG(year)相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff_big(DATE, DATE, OUT BIGINT);
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_datediff_big(DATE, DATE);

-- 7.6 删除CHARINDEX相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_charindex(TEXT, TEXT, OUT INT);
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_charindex(TEXT, TEXT);

-- 7.7 删除CAST(9/2 AS CHAR)相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_cast(OUT CHAR);
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_cast();

-- 7.8 删除TRY_CAST相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_try_cast(TEXT, OUT NUMERIC(10,5));
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_try_cast(TEXT);

-- 7.9 删除CONVERT相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_convert(INT, OUT VARCHAR);
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_convert(INT);

-- 7.10 删除TRY_CONVERT相关
DROP PROCEDURE IF EXISTS proc_D_Function_FOURTH_COMPATIBILITY_Case0012_try_convert(INT, OUT VARCHAR);
DROP FUNCTION IF EXISTS func_D_Function_FOURTH_COMPATIBILITY_Case0012_try_convert(INT);

-- 7.11 删除结果记录表
DROP TABLE IF EXISTS D_Function_FOURTH_COMPATIBILITY_Case0012_test_results;